{
  sqlite3 backend for kgs

  @Author    csyap
  @Version   2010.11.11 first version

  TODO, add entries of posting list
}
unit kgs_classes;

interface

uses classes,sysutils,sqlite3,sqlite3wrap;
type
  TKGSDatabase = class(TPersistent)
      fdb : TSQLite3Database;
      putrowstmt: TSQLite3Statement;
      putmetastmt: TSQLite3Statement;
      selectstmt:TSQLite3Statement;
      getrowstmt:TSQLite3Statement;
      putinvertedstmt: TSQLite3Statement;
      findtokenstmt:TSQLite3Statement;
    public
      constructor create(fn:widestring);
      destructor destroy;override;

      procedure createtables(tablename:widestring);
      procedure updateindexes(tablename:widestring);

      procedure createinverted(tablename:widestring);
      procedure doneinverted(tablename:widestring);
      procedure putinverted(tablename:widestring;token:widestring;
      inverted: pchar; size:integer;
      ms_offset,ms_count:Tmemorystream);

      procedure Execute(const SQL: WideString);

      procedure begintransaction;
      procedure rollback;
      procedure commit;
      procedure putrow(id,body:string);
      procedure putmeta(id,metaname,metavalue,metaextra:string);
      function fetch(var rowid:int64;var id,body:widestring):boolean;

      function getrowcount(tablename:widestring):integer;
      function getbyrowid(rowid:int64;var id,body:widestring):boolean;
      procedure select(tablename:widestring);
      function hastable(tablename:widestring):boolean;
      function findtoken(token:widestring;
               inverted,ms_offset,ms_count:TmemoryStream):boolean;
  end;

implementation

{ TKSEDatabase }
function invertedtablename(tablename:widestring):widestring;
begin
  result:=tablename+'_inverted';
end;
function TKGSDatabase.hastable(tablename:widestring):boolean;
var
  stmt:TSqlite3Statement;
begin
  stmt:=fdb.Prepare('SELECT * FROM sqlite_master WHERE name=?');
  stmt.BindText(1,tablename);
  result:= stmt.Step=SQLITE_ROW ;
end;
procedure TKGSDatabase.begintransaction;
begin
  fdb.BeginTransaction;
end;

procedure TKGSDatabase.commit;
begin
  fdb.Commit;
end;

constructor TKGSDatabase.create(fn: widestring);
begin
  fdb:=TSQLite3Database.Create;
  fdb.Open(fn);
  fdb.Execute('PRAGMA encoding = "UTF-16le";');
end;

destructor TKGSDatabase.destroy;
begin
  freeandnil(putrowstmt);
  freeandnil(putmetastmt);
  freeandnil(selectstmt);
  freeandnil(getrowstmt);
  freeandnil(putinvertedstmt);
  freeandnil(findtokenstmt);
  freeandnil(fdb);
  inherited;
end;


procedure TKGSDatabase.Execute(const SQL: WideString);
begin
  fdb.Execute(sql);
end;
procedure TKGSDatabase.createtables(tablename:widestring);
begin
  fdb.Execute('CREATE TABLE IF NOT EXISTS '+tablename+' (id,body) ');
  fdb.Execute('CREATE TABLE IF NOT EXISTS '+tablename+'_meta (id,name,value,extra)');
  fdb.Execute('DROP INDEX IF EXISTS '+tablename+'_id');
  fdb.Execute('DROP INDEX IF EXISTS '+tablename+'_meta_id');

  putrowstmt:=fdb.Prepare('INSERT INTO '+tablename+' VALUES (?,?)');
  putmetastmt:=fdb.Prepare('INSERT INTO '+tablename+'_meta VALUES (?,?,?,?)');
end;
procedure TKGSDatabase.putrow(id,body:string);
begin
  putrowstmt.BindText(1,utf8decode(id));
  putrowstmt.BindText(2,utf8decode(body));
  putrowstmt.StepAndReset;
end;
procedure TKGSDatabase.putmeta(id,metaname,metavalue,metaextra:string);
begin
  putmetastmt.BindText(1,utf8decode(id));
  putmetastmt.BindText(2,utf8decode(metaname));
  putmetastmt.BindText(3,utf8decode(metavalue));

  if metaextra<>'' then putmetastmt.BindText(4,utf8decode(metaextra))
  else putmetastmt.BindNull(4);
  putmetastmt.StepAndReset;
end;
procedure TKGSDatabase.rollback;
begin
  fdb.Rollback;
end;

procedure TKGSDatabase.updateindexes(tablename: widestring);
begin
  fdb.Execute('CREATE INDEX '+tablename+'_id on '+tablename+' (id)');
  fdb.Execute('CREATE INDEX '+tablename+'_meta_id on '+tablename+'_meta (id)');
end;
function TKGSDatabase.getrowcount(tablename:widestring):integer;
var
  stmt:TSQLite3Statement;
begin
  stmt:=fdb.Prepare('SELECT count(*) FROM '+tablename);
  stmt.step;
  result:=stmt.columnInt64(0);
  freeandnil(stmt);
end;
function TKGSDatabase.fetch(var rowid: int64; var id,
  body: widestring): boolean;
begin

  result:=selectstmt.Step=SQLITE_ROW;
  rowid:=selectstmt.ColumnInt64(0);
  id:=selectstmt.ColumnText(1);
  body:=selectstmt.ColumnText(2);
end;

procedure TKGSDatabase.select(tablename:widestring);
begin
   if assigned(selectstmt) then freeandnil(selectstmt);
   selectstmt:=fdb.Prepare('SELECT rowid,id,body FROM '+tablename);
   selectstmt.Reset;

   getrowstmt:=fdb.Prepare('SELECT id,body FROM '+tablename+' WHERE rowid=?');
   getrowstmt.Reset;


   if assigned(findtokenstmt) then freeandnil(findtokenstmt);
   if hastable(invertedtablename(tablename)) then begin
     findtokenstmt:=fdb.prepare('SELECT inverted,milestone_offset,milestone_count '+
     ' FROM '+invertedtablename(tablename) +' WHERE token=?');
   end;
end;

procedure TKGSDatabase.createinverted(tablename: widestring);
begin

  fdb.Execute('DROP TABLE IF EXISTS '+invertedtablename(tablename));
  fdb.Execute('CREATE TABLE '+invertedtablename(tablename)+
   '(token,inverted blob,milestone_offset blob, milestone_count blob)');
  fdb.Execute('DROP INDEX IF EXISTS '+invertedtablename(tablename)+'_token');

  putinvertedstmt:=fdb.Prepare('INSERT INTO '+invertedtablename(tablename)+' VALUES (?,?,?,?)');

end;

procedure TKGSDatabase.doneinverted(tablename:widestring);
begin
  fdb.Execute('CREATE INDEX '+invertedtablename(tablename)+'_token on '+invertedtablename(tablename)+' (token)');
end;
procedure TKGSDatabase.putinverted(tablename, token: widestring;
  inverted: pchar; size:integer;
  ms_offset,ms_count:Tmemorystream);
begin
  putinvertedstmt.BindText(1,token);
  putinvertedstmt.BindBlob(2,inverted,size);

  putinvertedstmt.BindBlob(3,ms_offset.memory,ms_offset.size);
  putinvertedstmt.BindBlob(4,ms_count.memory,ms_count.size);

  putinvertedstmt.StepAndReset;
end;

//fetch token information and write to memory stream.
function TKGSDatabase.findtoken(token: widestring;
inverted,ms_offset,ms_count:TmemoryStream): boolean;
var
  ptr:Pchar;
  size:cardinal;
begin
  result:=false;
  if not assigned(findtokenstmt) then exit; //no inverted index

  findtokenstmt.BindText(1,token);
  if findtokenstmt.Step<>SQLITE_ROW then exit;
  result:=true;

  ptr:=findtokenstmt.ColumnBlob(0);
  size:=findtokenstmt.Columnbytes(0);
  inverted.write(ptr^,size);

  ptr:=findtokenstmt.ColumnBlob(1);
  size:=findtokenstmt.Columnbytes(1);
  if size>0 then ms_offset.write(ptr^,size);

  ptr:=findtokenstmt.ColumnBlob(2);
  size:=findtokenstmt.Columnbytes(2);
  if size>0 then ms_count.write(ptr^,size);

  findtokenstmt.reset; // free up memory
end;

function TKGSDatabase.getbyrowid( rowid: int64;
  var id, body: widestring): boolean;
begin
  getrowstmt.Reset;
  getrowstmt.BindInt64(1,rowid);
  if getrowstmt.Step<>SQLITE_ROW then exit;
  result:=true;

  id:=getrowstmt.ColumnText(0);
  body:=getrowstmt.ColumnText(1);
end;

end.
